// 加载Express模块
const express = require('express');

// 加载MySQL模块
const mysql = require('mysql');

// 加载bodyParser模块
const bodyParser = require('body-parser');

// 创建MySQL连接池
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  password: '',        //数据库用户密码
  database: 'test',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8'      //数据库服务器的编码方式
});

// 创建服务器对象
const server = express();

server.use(bodyParser.urlencoded({
  extended: false
}));

// 加载CORS模块
const cors = require('cors');

// 使用CORS中间件
server.use(cors({
  origin: ['http://localhost:8080', 'http://127.0.0.1:8080',`https://api.binstd.com`]
}));

// 查询
server.get('/user/findAll', (req, res,next) => {
  let sql='select*from user';
	pool.query(sql,(err,result)=>{
		if (err) {
			next(err);
			return;  
		}
		res.send(result);
	})
});

// 新增
server.post('/user/save', (req, res) => {
  let EMPNO = req.body.EMPNO;
  let ENAME = req.body.ENAME;
  let JOB = req.body.JOB;
  let MGR = req.body.MGR;
  let HIREDATE = req.body.HIREDATE;
  let SAL = req.body.SAL;
  let COMM = req.body.COMM;
  let DEPTNO = req.body.DEPTNO;
  //以EMPNO为条件进行查找操作，以保证用户名的唯一性
  let sql = 'SELECT COUNT(EMPNO) AS count FROM USER WHERE ENAME=?';
  pool.query(sql, [ENAME], (error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = 'INSERT USER(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(?,?,?,?,?,?,?,?)';
      pool.query(sql, [EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 });
      })
    } else {
      res.send({ message: '员工编号不唯一', code: 400 });
    }
  });
});

// 删除
server.delete('/user/del/:EMPNO', (req, res,next) => {
  let _EMPNO=req.params.EMPNO;
	let sql="delete from user where EMPNO=?";
	pool.query(sql,[_EMPNO],(err,result)=>{
		if(err) {
			next(err);
			return;  
		}
		if(result.affectedRows>0){
			res.send({ message: 'ok', code: 200 });
		}else{
			res.send("0");
		}
	});
});

// 修改
server.put('/user/update', (req, res,next) => {
  let obj=req.body;
	let sql="update user set ENAME=?,JOB=?,MGR=?,HIREDATE=?,SAL=?,COMM=?,DEPTNO=? where EMPNO=?"; 
	pool.query(sql,[obj.ENAME,obj.JOB,obj.MGR,obj.HIREDATE,obj.SAL,obj.COMM,obj.DEPTNO,obj.EMPNO],(err,result)=>{
		if(err){
			next(err);
			return;  
		}
		if(result.affectedRows>0){
			res.send({ message: 'ok', code: 200 });
		}else{
			res.send("0");
		}
	});
});

// 指定服务器对象监听的端口号
server.listen(3000, () => {
  console.log('server is running...');
});